It is all to easy to build a database on a server and then expect it to be fully functional. Not so fast: There are a number of possibilities in terms of server-based functionality that can defeat you. Databases are likely to have scheduled jobs and alerts, and may have server triggers or even message queues. They are going to have different credentials and logins, proxy accounts, shared schedules and so on. Database applications will sometimes consist of several database components or may require linked servers to be set up. For this reason, it is important to be certain what this server-based functionality is, where it is, and to ensure that it is scripted out into source control so it can be used when provisioning a server for the application.

I’ve written in the past about how to script out server code. However, there is a different problem which is to work out the difference in configuration between two servers. Once you know the differences between the server configuration of a SQL Server instance that holds a working database application and the instance on which you wish to build a version of the application, then you can produce the relevant scripts to provide the database with the required working environment. This is a fairly common DevOps requirement, but one that seems to have few tools to help with the task. The possible reasons for this become apparent as soon as you look at the settings, properties and attributes of the server. Not only are there a great number of them, but few are likely to be relevant. When you are just starting out with this problem, It is much better to have an overview of the differences rather than become overwhelmed with a tsunami of possibly irrelevant data.

To get an overview, I prefer to examine what SMO regards as the server collections. These include such things as databases, endpoints, agent jobs, alerts and linked servers. The simplest comparisons are on the actual names. This tells you whether the two servers have, for example, a linked server of the same name. It is a start, but two servers can have, say, the same agent job with the same name, but that do different things, or are at different versions. You will still need to script them out and check that the scripts are the same, though you should then beware of false negatives due to headers with dates in them.-scripts will show up as different when the difference is actually just the date you did the scripting!

There is an important distinction to be made. The server can consist of a range of objects, such as a database. If we examine the name, we can tell fairly well what databases should be on the server we are provisioning. However, if you compare two databases, you can say whether the names are the same or different, but we are not saying whether the databases with the same name are identical or different. It is the same with settings: we can say that servers have the same settings, but they may have wildly different values. Now the fact that they have different values may, or may not, be important to the provisioning process. It would be foolhardy to say what is important because that will depend on your circumstances. After all, the fact that the server has a different name is very unlikely to be interesting.

We therefore will concentrate, in this script, on comparing the names of the objects in the various collections. This will at least tell you if a component is missing and is a lot quicker than tooling about with SSMS’s object browser!

For this work, there is a useful built-in Cmdlet called Compare-Object. Once you’ve understood the way it works it is very handy for doing comparisons. Its only problem is that it is not the most intuitive visual way of reporting differences, so we use the Compare-Object’s ‘SideIndicator’ for building up the results and then convert the results in one go, into something that is easier to understand. Note that I’m not listing objercts that are equal, just those that exist only on one server or the other. That is a knob you can twiddle but I’d only do that where there are likely to be just a limited number of objects.

Obviously, this would be wrapped into a function in general use. I’ve unwrapped it here to make it easier to investigate.

Here is a result from running it on a couple of development servers. The first two columns have the names of the two servers at the top, and the third column has the type of object we’re investigating